import openpyxl

class OperateExcel:

    @staticmethod
    def create_excel(file_path,sheet_name):
        workbook = openpyxl.Workbook()
        ws = workbook.active
        ws.title = sheet_name
        workbook.save(file_path)
        workbook.close()

    # 建一个sheet
    @staticmethod
    def create_sheet(file_path,sheet_name):
        workbook = openpyxl.load_workbook(file_path)
        workbook.create_sheet(sheet_name)
        workbook.save(file_path)
        workbook.close()

    @staticmethod
    def create_sheet_by_index(file_path,sheet_name,index):
        workbook = openpyxl.load_workbook(file_path)
        workbook.create_sheet(sheet_name,index)
        workbook.save(file_path)
        workbook.close()

    @staticmethod
    def write_excel(file_path,sheet_name,data):
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook[sheet_name]
        for i in range(len(data)):
            for j in range(len(data[i])):
                sheet.cell(row=i+1,column=j+1).value = data[i][j]
        workbook.save(file_path)
        workbook.close()

    @staticmethod
    def read_excel(file_path,sheet_name):
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook[sheet_name]
        data = [tuple(sheet.cell(row=i,column=j).value for j in range(1,sheet.max_column+1)) for i in range(1,sheet.max_row+1)]
        return data

    @staticmethod
    def read_excel_by_row(file_path,sheet_name):
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook[sheet_name]
        data = [(sheet.cell(row=i,column=j).value for j in range(1,sheet.max_column+1)) for i in range(2,sheet.max_row+1)]
        return data
